package com.yy.db;

import com.yy.pojo.Student;

import java.util.ArrayList;
import java.util.List;

public class StudentDBUtil extends DBUtil{

    //获取所有学生信息
    public List<Student> getAllStus(){
        List<Student> list=new ArrayList<>();
        try {
            getConn();
            String sql="select * from `student`";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Student stu=new Student(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)
                );
                list.add(stu);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConn();
        }
        return list;
    }

    public List<Student> getAllStusBySname(String sname) {
        sname="%"+sname+"%";
            List<Student> list=new ArrayList<>();
            try {
                getConn();
                String sql="select * from `student` where sname like ?";
                ps=conn.prepareStatement(sql);
                ps.setObject(1,sname);
                rs=ps.executeQuery();
                while(rs.next()){
                    Student stu=new Student(
                            rs.getInt(1),
                            rs.getString(2),
                            rs.getString(3),
                            rs.getString(4)
                    );
                    list.add(stu);
                }
            }catch(Exception e){
                e.printStackTrace();
            }finally {
                closeConn();
            }
            return list;
    }

    public int  deleteStuBySid(Integer sid) {
        int i=0;
        try{
            getConn();
            String sql="delete from `student` where sid=?";
            ps=conn.prepareStatement(sql);
            ps.setObject(1,sid);
           i= ps.executeUpdate();

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            closeConn();
        }
        return i;
    }
    //添加学生功能
    public int addStu(Student student){
        int i=0;
        try {
            getConn();
            String sql="insert into `student` (sname,sex,address) values(?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setObject(1,student.getSname());
            ps.setObject(2, student.getSex());
            ps.setObject(3,student.getAddress());
            i=ps.executeUpdate();
            if(i>0){
                System.out.println("添加学生成功");
            }else{
                System.out.println("添加学生失败");
            }
        }catch(Exception e){
        e.printStackTrace();
        }finally{
            closeConn();
        }
        return i;
    }
    //编辑学生信息时学生信息回显
    public Student getStuBySid(Integer sid){
        Student stu=null;
        try{
            getConn();
            String sql="select sid,sname,sex,address from `student` where sid=?";
            ps=conn.prepareStatement(sql);
            ps.setObject(1, sid);
            rs=ps.executeQuery();
            if(rs.next()){
                stu=new Student(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)
                        );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            closeConn();
        }
        return stu;
    }
//更新学生信息
    public int editStu(Student stu) {
        int i=0;
        try {
            getConn();
            String sql="update `student` set sname=?,sex=?,address=? where sid=?";
            ps=conn.prepareStatement(sql);
            ps.setObject(1, stu.getSname());
            ps.setObject(2, stu.getSex());
            ps.setObject(3, stu.getAddress());
            ps.setObject(4, stu.getSid());
            i=ps.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            closeConn();
        }
        return i;
    }
    //分页查询
    public List<Student> getAllStusByPage(Integer pageNum,Integer pageSize){
        int begin=(pageNum-1)*pageSize;
        List<Student> list=new ArrayList<>();
        try {
            getConn();
            String sql="select * from `student` limit ?,?";
            ps=conn.prepareStatement(sql);
            ps.setObject(1, begin);
            ps.setObject(2, pageSize);
            rs=ps.executeQuery();
            while(rs.next()){
                Student stu=new Student(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)
                );
                list.add(stu);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConn();
        }
        return list;
    }
    //得到总页数
    public int getTotalPages(Integer pageSize){
        int totalPages=0;
        int totalCount=0;
        try{
            getConn();
            String sql="select count(*) from `student`";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            if(rs.next()){
                totalCount=rs.getInt(1);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConn();
        }
        if(totalCount%pageSize==0){
            totalPages=totalCount/pageSize;
        }else{
            totalPages=totalCount/pageSize+1;
        }
        return totalPages;
    }
//分页展示查询的
    public List<Student> getStuByPageAndSname(int pageNumInt, Integer pageSize, String sname) {
        return null;
    }
}
